CREATE TABLE [dbo].[USER] (
    [USER_ID]       INT          IDENTITY (1, 1) NOT NULL,
    [USERNAME]      VARCHAR (25) NOT NULL,
    [PASSWORD]      VARCHAR (64) NOT NULL,
    [STATUS]        VARCHAR (25) NOT NULL,
	[FAMILY_ID]	    INT			 NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([USER_ID] ASC),
	CONSTRAINT [FK_USER_FAMILY] FOREIGN KEY ([FAMILY_ID]) REFERENCES [dbo].[FAMILY] ([FAMILY_ID])
);

CREATE TABLE [dbo].[VOLUNTEER_CONTROL_LIST] (
    [VCL_ID]        INT           IDENTITY (1, 1) NOT NULL,
    [ORG_ID]        INT           NOT NULL,
    [LIST_DESC]     VARCHAR (512) NOT NULL,
    [STATUS]        VARCHAR (25)  NOT NULL,
    [CREATED_DATE]  DATETIME      NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME      NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([VCL_ID] ASC)
);

CREATE TABLE [dbo].[FAMILY] (
    [FAMILY_ID]     INT          IDENTITY (1, 1) NOT NULL,
    [FAMILY_NAME]   VARCHAR (100) NOT NULL,
    [FAMILY_HEAD1]  INT          NULL,
    [FAMILY_HEAD2]  INT          NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([FAMILY_ID] ASC)
);

CREATE TABLE [dbo].[ORGANIZATION] (
    [ORG_ID]        INT          IDENTITY (1, 1) NOT NULL,
    [ORG_NAME]      VARCHAR (100) NOT NULL,
    [STATUS]        VARCHAR (25) NOT NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([ORG_ID] ASC)
);

CREATE TABLE [dbo].[USER_CONTACT] (
    [USER_CONTACT_ID] INT          IDENTITY (1, 1) NOT NULL,
    [USER_ID]         INT          NOT NULL,
    [FIRST_NAME]      VARCHAR (30) NOT NULL,
    [LAST_NAME]       VARCHAR (30) NOT NULL,
    [EMAIL]           VARCHAR (128) NOT NULL,
    [PHONE]           VARCHAR (32)  NULL,
    [CARRIER]         VARCHAR (25)  NULL,
    [CREATED_DATE]    DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]   DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([USER_CONTACT_ID] ASC),
    CONSTRAINT [FK_USER_CONTACT_USER] FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USER] ([USER_ID])
);

CREATE TABLE [dbo].[EVENT_CAT] (
    [EVENT_CAT_ID]   INT           IDENTITY (1, 1) NOT NULL,
    [EVENT_CAT_NAME] VARCHAR (50)  NOT NULL,
    [EVENT_CAT_DESC] VARCHAR (512) NOT NULL,
    [LOGO]           VARCHAR (250) NULL,
    [STATUS]         VARCHAR (25)  NOT NULL,
    [CREATED_DATE]     DATETIME      NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]    DATETIME      NOT NULL DEFAULT GETDATE(),
    [NEED_FLAG]      BIT           NOT NULL,
    [VCL_ID]         INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([EVENT_CAT_ID] ASC),
    CONSTRAINT [FK_EVENT_CAT_VOLUNTEER_CONTROL_LIST] FOREIGN KEY ([VCL_ID]) REFERENCES [dbo].[VOLUNTEER_CONTROL_LIST] ([VCL_ID])
);

CREATE TABLE [dbo].[EVENT] (
    [EVENT_ID]         INT           IDENTITY (1, 1) NOT NULL,
    [EVENT_CAT_ID]     INT           NOT NULL,
    [NAME]             VARCHAR (50)  NOT NULL,
    [EVENT_DESC]       VARCHAR (512) NOT NULL,
    [OWNER_ID]         INT           NOT NULL,
    [START_DATE]       DATETIME      NOT NULL,
    [END_DATE]         DATETIME      NOT NULL,
    [STATUS]           VARCHAR (25)  NOT NULL,
    [NUM_GUESTS]       INT           NOT NULL,
    [MIN_PARTICIPANTS] INT           NOT NULL,
    [CREATED_DATE]     DATETIME      NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]    DATETIME      NOT NULL DEFAULT GETDATE(),
    [NEED_FLAG]        BIT           NOT NULL,
    [VCL_ID]           INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([EVENT_ID] ASC),
    CONSTRAINT [FK_EVENT_EVENT_CAT] FOREIGN KEY ([EVENT_CAT_ID]) REFERENCES [dbo].[EVENT_CAT] ([EVENT_CAT_ID]),
    CONSTRAINT [FK_EVENT_VOLUNTEER_CONTROL_LIST] FOREIGN KEY ([VCL_ID]) REFERENCES [dbo].[VOLUNTEER_CONTROL_LIST] ([VCL_ID])
);

CREATE TABLE [dbo].[EVENT_SIGNUP] (
    [SIGNUP_ID]     INT          IDENTITY (1, 1) NOT NULL,
    [EVENT_ID]      INT          NOT NULL,
    [USER_ID]       INT          NOT NULL,
    [GUEST_FLAG]    BIT     NOT NULL,
    [NUM_GUESTS]    INT          NOT NULL,
    [STATUS]        VARCHAR (25) NOT NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([SIGNUP_ID] ASC),
    CONSTRAINT [FK_EVENT_SIGNUP_EVENT] FOREIGN KEY ([EVENT_ID]) REFERENCES [dbo].[EVENT] ([EVENT_ID]),
    CONSTRAINT [FK_EVENT_SIGNUP_USER] FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USER] ([USER_ID])
);

CREATE TABLE [dbo].[EXTENDED_USER_INFO] (
    [EXT_USER_INFO_ID] INT          IDENTITY (1, 1) NOT NULL,
    [ORG_ID]           INT          NOT NULL,
    [NAME]             VARCHAR (50) NOT NULL,
    [DESC]             VARCHAR (512) NOT NULL,
    [DATA_FIELDS]      VARCHAR (256) NOT NULL,
    [STATUS]           VARCHAR (25) NOT NULL,
    [CREATED_DATE]     DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]    DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([EXT_USER_INFO_ID] ASC),
    CONSTRAINT [FK_EXTENDED_USER_INFO_ORGANIZATION] FOREIGN KEY ([ORG_ID]) REFERENCES [dbo].[ORGANIZATION] ([ORG_ID])
);

CREATE TABLE [dbo].[EXT_USER_INFO_DETAILS] (
    [USER_DETAILS_ID]  INT          IDENTITY (1, 1) NOT NULL,
    [EXT_USER_INFO_ID] INT          NOT NULL,
    [USER_ID]          INT          NOT NULL,
    [START_DATE]       DATETIME     NOT NULL,
    [EXPIRATION_DATE]  DATETIME     NOT NULL,
    [STATUS]           VARCHAR (25) NOT NULL,
    [CREATED_DATE]     DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]    DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([USER_DETAILS_ID] ASC),
    CONSTRAINT [FK_EXT_USER_INFO_DETAILS_EXTENDED_USER_INFO] FOREIGN KEY ([EXT_USER_INFO_ID]) REFERENCES [dbo].[EXTENDED_USER_INFO] ([EXT_USER_INFO_ID]),
    CONSTRAINT [FK_EXT_USER_INFO_DETAILS_USER] FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USER] ([USER_ID])
);

CREATE TABLE [dbo].[USER_EVENT_PREF] (
    [PREF_ID]        INT          IDENTITY (1, 1) NOT NULL,
    [EVENT_CAT_ID]   INT          NOT NULL,
    [USER_ID]        INT          NOT NULL,
    [INTEREST_LEVEL] INT          NOT NULL,
    [STATUS]         VARCHAR (25) NOT NULL,
    [CREATED_DATE]   DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([PREF_ID] ASC),
    CONSTRAINT [FK_USER_EVENT_PREF_EVENT_CAT] FOREIGN KEY ([EVENT_CAT_ID]) REFERENCES [dbo].[EVENT_CAT] ([EVENT_CAT_ID]),
    CONSTRAINT [FK_USER_EVENT_PREF_USER] FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USER] ([USER_ID])
);

CREATE TABLE [dbo].[USER_ROLE] (
    [USER_ROLE_ID]  INT          IDENTITY (1, 1) NOT NULL,
    [ROLE_NAME]     VARCHAR (50) NOT NULL,
    [STATUS]        VARCHAR (25) NOT NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    [USER_ID]       INT          NOT NULL,
    [ORG_ID]        INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([USER_ROLE_ID] ASC),
    CONSTRAINT [FK_USER_ROLE_ORGANIZATION] FOREIGN KEY ([ORG_ID]) REFERENCES [dbo].[ORGANIZATION] ([ORG_ID]),
    CONSTRAINT [FK_USER_ROLE_USER] FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USER] ([USER_ID])
);

CREATE TABLE [dbo].[VCL_MEMBERS] (
    [VCL_MEMBER_ID] INT          IDENTITY (1, 1) NOT NULL,
    [VCL_ID]        INT          NOT NULL,
    [USER_ID]       INT          NOT NULL,
    [STATUS]        VARCHAR (25) NOT NULL,
    [CREATED_DATE]  DATETIME     NOT NULL DEFAULT GETDATE(),
    [LAST_MOD_DATE] DATETIME     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY CLUSTERED ([VCL_MEMBER_ID] ASC),
    CONSTRAINT [FK_VCL_MEMBERS_VOLUNTEER_CONTROL_LIST] FOREIGN KEY ([VCL_ID]) REFERENCES [dbo].[VOLUNTEER_CONTROL_LIST] ([VCL_ID])
);